ALTER PROCEDURE [dbo].[sp_global_instructions_forecasts_detail]
	@customer_id					INT, 
	@item_id						VARCHAR(16),
	@year							INT
AS

/*
	Tabla base con la situacion actual de los compromisos de compra
*/
CREATE TABLE #forecast_output (
	purchase_instruction_detail_id	INT NULL DEFAULT NULL, 
	instruction_forecast_id			INT NULL DEFAULT NULL, 
	customer_id						INT NOT NULL DEFAULT 0, 
	item_id							CHAR(16) NOT NULL DEFAULT '', 
	month_period_id					INT NOT NULL DEFAULT 0, 
	[year]							SMALLINT NOT NULL DEFAULT 0, 
	[month]							TINYINT NOT NULL DEFAULT 0, 
	future_period_id				INT NOT NULL DEFAULT 0, 
	contracted						DECIMAL(18,3) NOT NULL DEFAULT 0, 
	corn_contracted					DECIMAL(18,3) NOT NULL DEFAULT 0, 
	non_contracted					DECIMAL(18,3) NOT NULL DEFAULT 0, 
	non_corn_contracted				DECIMAL(18,3) NOT NULL DEFAULT 0, 
	factor							DECIMAL(18,3) NOT NULL DEFAULT 0,
	forecast_added					TINYINT NOT NULL DEFAULT 0, 
	saved_contracted				DECIMAL(18,3) NULL DEFAULT NULL,	--toneladas de producto salvadas en la instruccion
	actual_forecast					DECIMAL(18,3) NOT NULL DEFAULT 0, 
	covered							DECIMAL(18,4) NOT NULL DEFAULT 0
)

INSERT INTO #forecast_output (purchase_instruction_detail_id, instruction_forecast_id, 
		customer_id, item_id, month_period_id, [year], [month], future_period_id, 
		contracted, corn_contracted, non_contracted, non_corn_contracted, factor, 
		forecast_added, saved_contracted, actual_forecast, covered)
	SELECT NULL purchase_instruction_detail_id, NULL instruction_forecast_id, 
			sf.customer_id, sf.item_id, 
			mp.month_period_id, mp.year, mp.month, fp.future_period_id,
			sf.contracted, (sf.contracted * ISNULL(sf.factor, 0)) corn_contracted, 
			sf.non_contracted, (sf.non_contracted * ISNULL(sf.factor, 0)) non_corn_contracted, 
			ISNULL(sf.factor, 0) factor, 0 forecast_added, NULL saved_contracted, sf.contracted actual_forecast, 
			0 covered
		FROM vw_sales_forecasts_factors sf, months_periods mp, futures_periods fp
		WHERE sf.month_period_id = mp.month_period_id
			AND fp.start_date <= mp.start_date
			AND mp.end_date <= fp.end_date 

/*
	Carga valores de las instrucciones
*/
UPDATE fo SET
		fo.forecast_added = 1,
		fo.purchase_instruction_detail_id = pid.purchase_instruction_detail_id, 
		fo.instruction_forecast_id = i.instruction_forecast_id, 
		fo.saved_contracted = i.qty_item_contracted
	FROM #forecast_output fo, purchases_instructions_details pid, purchases_instructions_items_details piid, instructions_forecasts i
	WHERE fo.customer_id = pid.customer_id and pid.purchase_instruction_detail_id = piid.purchase_instruction_detail_id
		AND fo.item_id = piid.item_id
		AND fo.month_period_id = i.month_period_id
		AND pid.purchase_instruction_detail_id = i.purchase_instruction_detail_id

/*
	Listado de futuros comprados por periodo - cliente - producto
*/
CREATE TABLE #futures_purchased (
	future_period_id				INT NOT NULL DEFAULT 0,
	customer_id						INT NOT NULL DEFAULT 0, 
	item_id							CHAR(16) NOT NULL DEFAULT '', 
	futures							DECIMAL(18,4) NOT NULL DEFAULT 0, 
	tons							DECIMAL(18,4) NOT NULL DEFAULT 0
)

-- alfa: futuros por cliente - producto
INSERT INTO #futures_purchased (future_period_id, customer_id, item_id, futures, tons)
	SELECT application_future_period_id, customer_id, item_id, 
			SUM(futures_weighted_avg) corn_futures_purchased, 
			dbo.fn_unit_convertion('CON', 'TON', SUM(futures_weighted_avg)) corn_tons_purchased
		FROM vw_customer_futures_weighted_avg
		GROUP BY application_future_period_id, customer_id, item_id


/*
	Listado de futuros colocados como estrategias de compra por periodo - cliente - producto
*/
CREATE TABLE #futures_planned (
	future_period_id				INT NOT NULL DEFAULT 0,
	customer_id						INT NOT NULL DEFAULT 0, 
	item_id							CHAR(16) NOT NULL DEFAULT '', 
	futures							DECIMAL(18,4) NOT NULL DEFAULT 0, 
	tons							DECIMAL(18,4) NOT NULL DEFAULT 0
)

--beta: seleccion de futuros en estrategias
INSERT INTO #futures_planned (future_period_id, customer_id, item_id, futures, tons)
	SELECT pif.application_future_period_id, pid.customer_id, piid.item_id, 
			SUM(pif.quantity) corn_futures_planned, 
			dbo.fn_unit_convertion('CON', 'TON', SUM(pif.quantity)) corn_tons_planned
		FROM purchases_instructions_details pid, purchases_instructions_items_details piid, purchases_instructions_futures pif
		WHERE pid.purchase_instruction_detail_id = pif.purchase_instruction_detail_id and pid.purchase_instruction_detail_id = piid.purchase_instruction_detail_id
		GROUP BY pif.application_future_period_id, pid.customer_id, piid.item_id

/*
	Listado de futuros comprados mediante una instruccion de compra por periodo - cliente - producto
*/
CREATE TABLE #futures_realized (
	future_period_id				INT NOT NULL DEFAULT 0,
	customer_id						INT NOT NULL DEFAULT 0, 
	item_id							CHAR(16) NOT NULL DEFAULT '', 
	futures							DECIMAL(18,4) NOT NULL DEFAULT 0, 
	tons							DECIMAL(18,4) NOT NULL DEFAULT 0
)

--gamma: seleccion de futuros instruccionados - realizados por periodo
INSERT INTO #futures_realized (future_period_id, customer_id, item_id, futures, tons)
	SELECT future_application_future_period_id, customer_id, item_id, 
			quantity corn_futures_realized, 
			dbo.fn_unit_convertion('CON', 'TON', quantity) corn_tons_realized
		FROM vw_customer_futures_realized


/*
	Listado de futuros ponderados segun los pronosticos de ventas por periodo mensual - cliente - producto
*/
CREATE TABLE #calcs (
	customer_id						INT NOT NULL DEFAULT 0, 
	item_id							CHAR(16) NOT NULL DEFAULT '', 
	month_period_id					INT NOT NULL DEFAULT 0, 
	future_period_id				INT NOT NULL DEFAULT 0,
	contracted						DECIMAL(18,4) NOT NULL DEFAULT 0, 
	period_contracted				DECIMAL(18,4) NOT NULL DEFAULT 0, 
	tons_purchased					DECIMAL(18,4) NOT NULL DEFAULT 0, 
	tons_planned					DECIMAL(18,4) NOT NULL DEFAULT 0, 
	tons_realized					DECIMAL(18,4) NOT NULL DEFAULT 0
)

INSERT INTO #calcs (customer_id, item_id, month_period_id, future_period_id, 
		contracted, period_contracted, tons_purchased, tons_planned, tons_realized)
	SELECT fo.customer_id, fo.item_id, fo.month_period_id, fo.future_period_id, 
			fo.contracted, SUM(fo2.contracted) period_contracted, 
			ISNULL((fpu.tons * (fo.contracted / SUM(fo2.contracted))), 0) tons_purchased, 
			ISNULL((fpl.tons * (fo.contracted / SUM(fo2.contracted))), 0) tons_planned, 
			ISNULL((fr.tons * (fo.contracted / SUM(fo2.contracted))), 0) tons_realized
		FROM #forecast_output fo
			INNER JOIN #forecast_output fo2
				ON fo.future_period_id = fo2.future_period_id
				AND fo.customer_id = fo2.customer_id
				AND fo.item_id = fo2.item_id
			LEFT OUTER JOIN #futures_purchased fpu
				ON fo.future_period_id = fpu.future_period_id
				AND fo.customer_id = fpu.customer_id
				AND fo.item_id = fpu.item_id
			LEFT OUTER JOIN #futures_planned fpl
				ON fo.future_period_id = fpl.future_period_id
				AND fo.customer_id = fpl.customer_id
				AND fo.item_id = fpl.item_id
			LEFT OUTER JOIN #futures_realized fr
				ON fo.future_period_id = fr.future_period_id
				AND fo.customer_id = fr.customer_id
				AND fo.item_id = fr.item_id
		GROUP BY fo.customer_id, fo.item_id, fo.month_period_id, fo.future_period_id, 
			fo.contracted, fpu.tons, fpl.tons, fr.tons
		HAVING SUM(fo2.contracted) > 0


/*
	Actualizaci[on del porcentaje de covertura
*/
UPDATE #forecast_output SET covered = 1 - (contracted / actual_forecast)
	WHERE actual_forecast > 0

/*
	Actualizacion de cantidades de producto solicitado y de maiz equivalente
segun lo ya comprado y lo proyectado en estrategias
*/

UPDATE fo SET
		fo.contracted = fo.contracted - c.tons_purchased - c.tons_planned - c.tons_realized, 
		fo.corn_contracted = (fo.contracted - c.tons_purchased - c.tons_planned - c.tons_realized) * fo.factor
	FROM #forecast_output fo, #calcs c
	WHERE fo.customer_id = c.customer_id
		AND fo.item_id = c.item_id
		AND fo.month_period_id = c.month_period_id

UPDATE #forecast_output SET covered = 1 - (contracted / actual_forecast)
	WHERE actual_forecast > 0

SET NOCOUNT OFF

SELECT FO.YEAR,  fo.month, FO.CUSTOMER_ID, FO.ITEM_ID, 
	FO.CONTRACTED, 
	FO.NON_CONTRACTED, 
	FO.CORN_CONTRACTED, 
	FO.NON_CORN_CONTRACTED, 
	c.description customer, i.description item, 
	FO.FORECAST_ADDED,
	CASE
		WHEN ACTUAL_FORECAST > 0 THEN ( 1 - (CONTRACTED / ACTUAL_FORECAST) ) * 100
		ELSE 0
	END AS COVERED
FROM #forecast_output fo, vw_customers c,
		vw_items i
WHERE fo.customer_id = c.customer_id
		AND fo.item_id = i.item_id
		and fo.customer_id = @customer_id 
		AND fo.item_id = @item_id
		AND fo.year = @year
order by fo.month	
